package com.util;

/**
 * 
 * @author Satya Deepthi Bhagi
 */

public class RuntimeSettings {
	public static final String APPLICATION_NAME = "WorkFlowManagement";
	public static final String VERSION_ID = " version 0.1 dated December 28, 2012";
	public static final String SERVER_IP = "localhost";
	static String databaseName = "wfmanage";
	static String dbUserID = "root";
	static String dbPassword = "mysql"; 
	public static String RUN_MODE = "Test"; // "Production"; //
	public static boolean IS_IN_DEBUG_MODE = true; // false; //
	static int portNo = 3306;
	public static String scriptSql[] = {
			// " drop database if exists WFMANAGE;",
			// " create database WFMANAGE;",
			"USE WFMANAGE;",
			// " drop table if exists REQUEST;",
		   // " create table REQUEST;",
			"DROP TABLE IF EXISTS REQUEST;",
			"CREATE TABLE  REQUEST ("
					+ "REQ_ID INT(20) unsigned NOT NULL AUTO_INCREMENT,"
					+ "USER_ID VARCHAR(20) NOT NULL,"
					+ "MODE_OF_TRAVEL VARCHAR(20) NOT NULL,"
					+ "TICKET_NUMBER VARCHAR(30) NOT NULL,"
					+ "TRAVELLED_FROM VARCHAR(50) NOT NULL,"
					+ "DESTINATION VARCHAR(50) NOT NULL,"
					+ "DISTANCE_KMS INT(10) NOT NULL,"
					+ "TRAVEL_DATE DATE NOT NULL,"	
					+ "TRAVELLING_FARE INT(10) NOT NULL,"
					+ "REQ_SUBMISSION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
					+ "VERIFY_DPT_STATUS VARCHAR(50) DEFAULT 'verfication pending',"
					+ "VERIFY_DPT_ACTION_TIME TIMESTAMP,"
					+ "VERIFY_DPT_COMMENT VARCHAR(200) DEFAULT 'NA',"
					+ "VERIFIED_BY VARCHAR(60) DEFAULT 'Verification Pool',"
					+ "FINANCE_DPT_STATUS VARCHAR(50) DEFAULT 'finance approval pending',"
					+ "FINANCE_DPT_ACTION_TIME TIMESTAMP,"
					+ "FINANCE_DPT_COMMENT VARCHAR(200) DEFAULT 'NA',"
					+ "FINANCE_APPROVAL_BY VARCHAR(60) DEFAULT 'Finance Pool',"
					+ "APPROVED_AMT INT(10) DEFAULT 0,"
					+ "STATUS VARCHAR(50) DEFAULT 'pending',"
					+ "PRIMARY KEY (`REQ_ID`));",
					
			// " drop table if exists USER;",
		   // " create table USER;",
					
			"DROP TABLE IF EXISTS EMPLOYEE;",
					"CREATE TABLE  EMPLOYEE ("
							+ "USER_ID VARCHAR(20) NOT NULL,"							
							+ "PASSWORD VARCHAR(20) NOT NULL,"
							+ "USER_NAME VARCHAR(100) NOT NULL,"
							+ "LTA_LIMIT INT(10) NOT NULL,"
							+ "EMAIL_ID VARCHAR(100) NOT NULL,"
							+ "EMP_ROLE VARCHAR(50) NOT NULL,"
							+ "PRIMARY KEY (`USER_ID`));",

//*********************************************************************************************************
//-------------------------------------------------------------sravvani code-------------------------------
//*********************************************************************************************************
			"DROP TABLE IF EXISTS DOMAIN;",
					"CREATE TABLE DOMAIN("
							+ "DOMAIN_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "DOMAIN_NAME VARCHAR(40) NOT NULL,"
							+ "IS_CURRENT BIT);",
			
			"DROP TABLE IF EXISTS ISSUE;",
					"CREATE TABLE ISSUE("
							+ "ISSUE_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "ISSUE_NAME VARCHAR(40) NOT NULL,"
							+ "IS_CURRENT BIT);",
							
			"DROP TABLE IF EXISTS ROLE;",
					"CREATE TABLE ROLE("
							+ "ROLE_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "ROLE_NAME VARCHAR(40) NOT NULL,"
							+ "IS_CURRENT BIT);",
			
			"INSERT INTO ROLE(ROLE_ID, ROLE_NAME) "
										+ "VALUES(1, 'HelpDesk_Admin');",
			"INSERT INTO ROLE(ROLE_ID, ROLE_NAME) "
									+ "VALUES(2, 'Support_Executive 1');",
			"INSERT INTO ROLE(ROLE_ID, ROLE_NAME) "
									+ "VALUES(3, 'Support_Executive 2');",
			"INSERT INTO ROLE(ROLE_ID, ROLE_NAME) "
									+ "VALUES(4, 'Support_Executive 3');",
			"INSERT INTO ROLE(ROLE_ID, ROLE_NAME) "
									+ "VALUES(5, 'HelpDesk_User');",
							
							
			"DROP TABLE IF EXISTS STATUS;",
					"CREATE TABLE STATUS("
							+ "STATUS_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "STATUS_NAME VARCHAR(40) NOT NULL,"
							+ "IS_CURRENT BIT);",
							"INSERT INTO STATUS(STATUS_ID, STATUS_NAME) "
									+ "VALUES(1, 'To be assigned');",
			"INSERT INTO STATUS(STATUS_ID, STATUS_NAME) "
									+ "VALUES(2, 'Assigned to 1');",
			"INSERT INTO STATUS(STATUS_ID, STATUS_NAME) "
					           		+ "VALUES(3, 'Assigned to 2');",
			"INSERT INTO STATUS(STATUS_ID, STATUS_NAME) "
							        + "VALUES(4, 'Assigned to 3');",
		    "INSERT INTO STATUS(STATUS_ID, STATUS_NAME) "
							        + "VALUES(5, 'Resolved');",

			"DROP TABLE IF EXISTS USER;",							
					"CREATE TABLE USER("
							+ "USER_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "USER_NAME VARCHAR(40) NOT NULL UNIQUE,"
							+ "PASSWORD VARCHAR(40) NOT NULL,"
							+ "EMAIL_ID VARCHAR(40) NOT NULL,"
							+ "PHONE_NO VARCHAR(15) NOT NULL,"
							+ "IS_CURRENT BIT);",
			"INSERT INTO USER(USER_ID,PASSWORD,USER_NAME,EMAIL_ID,IS_CURRENT,PHONE_NO) VALUES(1,'1234','Admin','wfmanage@gmail.com',1,'');",
			"INSERT INTO EMPLOYEE(USER_ID,PASSWORD,USER_NAME,LTA_LIMIT,EMAIL_ID,EMP_ROLE) VALUES(1,'1234','Admin',10000,'wfmanage@gmail.com','Admin');",
							
			"DROP TABLE IF EXISTS WORKFLOWPOINT;",	
					"CREATE TABLE WORKFLOWPOINT("
							+ "WORKFLOWPOINT_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "WORKFLOWPOINT_NAME VARCHAR(40) NOT NULL,"
							+ "IS_CURRENT BIT);",

			"DROP TABLE IF EXISTS CATEGORY;",
					 "CREATE TABLE CATEGORY("
							+ "CATEGORY_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "CATEGORY_NAME VARCHAR(40) NOT NULL,"
							+ "IS_CURRENT BIT);",

			"DROP TABLE IF EXISTS PRIORITY;",
					 "CREATE TABLE PRIORITY("
							+ "PRIORITY_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "PRIORITY_NAME VARCHAR(40) NOT NULL UNIQUE,"
							+ "IS_CURRENT BIT);",

			"DROP TABLE IF EXISTS DOMAIN_WORKFLOWPOINT;",
					 "CREATE TABLE DOMAIN_WORKFLOWPOINT("
							+ "DOMAIN_WORKFLOWPOINT_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "DOMAIN_ID INT(10) UNSIGNED NOT NULL,"
							+ "WORKFLOWPOINT_ID INT(10) UNSIGNED NOT NULL,"
							+ "INITIALWORKFLOWPOINT BIT,"
							+ "FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN(DOMAIN_ID),"
							+ "FOREIGN KEY (WORKFLOWPOINT_ID) REFERENCES WORKFLOWPOINT(WORKFLOWPOINT_ID),"
							+ "CONSTRAINT UC_DOMAIN_WORKFLOWPOINT UNIQUE (DOMAIN_ID ,WORKFLOWPOINT_ID));",

			"DROP TABLE IF EXISTS DOMAIN_WORKFLOWPOINT_ROLE;",
					 "CREATE TABLE DOMAIN_WORKFLOWPOINT_ROLE("
							+ "DOMAIN_WORKFLOWPOINT_ROLE_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "DOMAIN_ID INT(10) UNSIGNED NOT NULL,"
							+ "WORKFLOWPOINT_ID INT(10) UNSIGNED NOT NULL,"
							+ "ROLE_ID INT(10) UNSIGNED NOT NULL,"
							+ "DURATION INT(5) UNSIGNED NOT NULL,"
							+ "FOREIGN KEY (DOMAIN_ID) REFERENCES DOMAIN(DOMAIN_ID),"
							+ "FOREIGN KEY (WORKFLOWPOINT_ID) REFERENCES WORKFLOWPOINT(WORKFLOWPOINT_ID),"
							+ "FOREIGN KEY (ROLE_ID) REFERENCES ROLE(ROLE_ID),"
							+ "CONSTRAINT UC_DOMAIN_WORKFLOWPOINT_ROLE UNIQUE (DOMAIN_ID,WORKFLOWPOINT_ID, ROLE_ID));",
						
			"DROP TABLE IF EXISTS USER_ROLE;",			
					 "CREATE TABLE USER_ROLE("
							+ "USER_ROLE_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "USER_ID INT(10) UNSIGNED NOT NULL UNIQUE,"
							+ "ROLE_ID INT(10) UNSIGNED NOT NULL,"
							+ "FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID),"
							+ "FOREIGN KEY (ROLE_ID) REFERENCES ROLE(ROLE_ID),"
							+ "CONSTRAINT UC_USER_ROLE UNIQUE (USER_ID, ROLE_ID));",
							
			"DROP TABLE IF EXISTS PROGRESS_WORKFLOW;",
					 "CREATE TABLE PROGRESS_WORKFLOW("
							+ "PROGRESS_WORKFLOW_ID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
							+ "FROMWORKFLOWPOINT_ID INT(10) UNSIGNED NOT NULL,"
							+ "TOWORKFLOWPOINT_ID INT(10) UNSIGNED NOT NULL,"
							+ "FOREIGN KEY (FROMWORKFLOWPOINT_ID) REFERENCES WORKFLOWPOINT(WORKFLOWPOINT_ID),"
							+ "FOREIGN KEY (TOWORKFLOWPOINT_ID) REFERENCES WORKFLOWPOINT(WORKFLOWPOINT_ID),"
							+ "CONSTRAINT UC_PROGRESS_WORKFLOW UNIQUE (FROMWORKFLOWPOINT_ID,TOWORKFLOWPOINT_ID));",
							
//*********************************************************************************************************
//-------------------------------------------------------------sruti code-------------------------------
//*********************************************************************************************************
			"DROP TABLE IF EXISTS SUPPORT_PRIORITY;",
					"CREATE TABLE SUPPORT_PRIORITY ("
							+ "SUPPORT_PRIORITY_ID INT unsigned NOT NULL AUTO_INCREMENT,"
							+ "SUPPORT_ID INT unsigned NOT NULL,"
							+ "PRIORITY_ID INT(10) unsigned NOT NULL,"
							+ "PRIMARY KEY (SUPPORT_PRIORITY_ID),"
							+ "FOREIGN KEY (SUPPORT_ID) REFERENCES ROLE(ROLE_ID),"
							+ "FOREIGN KEY (PRIORITY_ID) REFERENCES PRIORITY(PRIORITY_ID));",
		
			
		    "INSERT INTO SUPPORT_PRIORITY(SUPPORT_PRIORITY_ID, SUPPORT_ID, PRIORITY_ID) VALUES(1, 2, 1);",
		    "INSERT INTO SUPPORT_PRIORITY(SUPPORT_PRIORITY_ID, SUPPORT_ID, PRIORITY_ID) VALUES(2, 3, 2);",
		    "INSERT INTO SUPPORT_PRIORITY(SUPPORT_PRIORITY_ID, SUPPORT_ID, PRIORITY_ID) VALUES(3, 4, 3);",
							
			"DROP TABLE IF EXISTS ASSIGNMENT;",
					"CREATE TABLE ASSIGNMENT ("
							+ "ASSIGNMENT_ID INT unsigned NOT NULL AUTO_INCREMENT,"
							+ "STATUS_ID INT(10) unsigned NOT NULL,"
							+ "SUPPORT_ID INT unsigned NOT NULL,"
							+ "PRIMARY KEY (ASSIGNMENT_ID),"
							+ "FOREIGN KEY (STATUS_ID) REFERENCES STATUS(STATUS_ID),"
							+ "FOREIGN KEY (SUPPORT_ID) REFERENCES ROLE(ROLE_ID));",

			"INSERT INTO ASSIGNMENT(ASSIGNMENT_ID, STATUS_ID, SUPPORT_ID) VALUES(1, 1, 1);",
			"INSERT INTO ASSIGNMENT(ASSIGNMENT_ID, STATUS_ID, SUPPORT_ID) VALUES(2, 2, 2);",
			"INSERT INTO ASSIGNMENT(ASSIGNMENT_ID, STATUS_ID, SUPPORT_ID) VALUES(3, 3, 3);",
			"INSERT INTO ASSIGNMENT(ASSIGNMENT_ID, STATUS_ID, SUPPORT_ID) VALUES(4, 4, 4);",

			"DROP TABLE IF EXISTS TICKET;",
					"CREATE TABLE TICKET ("
							+ "TICKET_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,"
							+ "USER_NAME  VARCHAR(100) NOT NULL,"
							+ "PHONE VARCHAR(15) NOT NULL,"
							+ "EMAIL VARCHAR(50) NOT NULL,"
							+ "DEPT VARCHAR(20) NOT NULL,"
							+ "OFFICE VARCHAR(20) NOT NULL,"
							+ "PROBLEM_CATEGORY VARCHAR(30) NOT NULL,"
							+ "DEVICE VARCHAR(40) NOT NULL,"
							+ "OS VARCHAR(30) NOT NULL,"
							+ "PRIORITY VARCHAR(40) NOT NULL, "
							+ "TICKET_DATE DATE NOT NULL,"
							+ "SUMMARY VARCHAR(50) NOT NULL,"
							+ "DESCRIPTION VARCHAR(200) NOT NULL,"
							+ "COMMENTS VARCHAR(100) DEFAULT 'NA',"
							+ "STATUS_ID INT(10) UNSIGNED NOT NULL,"
							+ "PRIMARY KEY (TICKET_ID),"
							+ "FOREIGN KEY (STATUS_ID) REFERENCES STATUS(STATUS_ID),"
							+ "FOREIGN KEY (PRIORITY) REFERENCES PRIORITY(PRIORITY_NAME));"
							
							
	    };

}
